//
//  SQLiteManager.swift
//  FMDB test
//
//  Created by 高辰 on 16/5/24.
//  Copyright © 2016年 itcast. All rights reserved.
//

import UIKit

//数据库名
let dbName = "weibo.db"

class SQLiteManager: NSObject {
    //创建单例
    static let manager: SQLiteManager = SQLiteManager()
    //数据库操作队列
    lazy var queue: FMDatabaseQueue = {
        let path = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
        print(path)
        return FMDatabaseQueue(path: path)
    }()
    //构造函数私有化
    private override init() {
        super.init()
        createTabel()
    }
    
    private func createTabel() {
        //1.准备SQL语句
        let sqlPath = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)
        
        let sql = try! String(contentsOfFile: sqlPath!)
        
        queue.inDatabase { (database) -> Void in
            let result = database.executeStatements(sql)
            if result {
                print("创表成功")
            }else {
                print("创表失败")
            }
        }
    }
    
    
    //插入数据
    func insert() {
        let sql = "INSERT INTO T_PERSON(NAME,AGE) VALUES(?,?)"
        
        queue.inDatabase { (database) -> Void in
            let result = database.executeUpdate(sql, withArgumentsInArray: ["Lucy", 18])
            if result {
                print("插入成功")
            }else {
                print("插入失败")
            }
        }
    }
    
    //查询数据
    //    select productName,productPrice from t_product;
    func select() {
        queue.inDatabase { (database) -> Void in
            let sql = "SELECT ID,NAME,AGE FROM T_PERSON"
            
            let resultSet = database.executeQuery(sql, withParameterDictionary: nil)
            
            while resultSet.next() {
                let id = resultSet.intForColumn("ID")
                let name = resultSet.stringForColumn("NAME")
                let age = resultSet.intForColumn("AGE")
                print("id = \(id), name = \(name), age = \(age)")
            }
            
        }
    }
    
    //通用查询
    func recordSet(sql: String) -> [[String: AnyObject]] {
        
        //定义可变的字典数组
        var tempArray = [[String: AnyObject]]()
        
        queue.inDatabase { (database) -> Void in
           let resultSet = database.executeQuery(sql, withParameterDictionary: nil)
            while resultSet.next() {
                var dict = [String: AnyObject]()
                for i in 0..<resultSet.columnCount() {
                    //获取列名
                    let columnName = resultSet.columnNameForIndex(i)
                    //获取列对应的值
                    let cilumnValue = resultSet.objectForColumnName(columnName)
                    //添加键值对到字典
                    dict[columnName] = cilumnValue
                }
                tempArray.append(dict)
            
            }
        }
        return tempArray
    }
    
    
    
    //修改数据
    func update() {
        queue.inDatabase { (database) -> Void in
            
            let sql = "UPDATE T_PERSON SET NAME = ? , AGE = ? WHERE ID = ?"
            let result = database.executeUpdate(sql, withArgumentsInArray: ["Tom", 20 , 2])
            
            if result {
                print("修改成功")
            }else {
                print("修改失败")
            }
        }
    }
    
    //删除数据
    func delete() {
        queue.inDatabase { (database) -> Void in
            let sql = "DELETE FROM T_PERSON WHERE ID = ?"
            let result = database.executeUpdate(sql, withArgumentsInArray: [2])
            if result {
                print("删除成功")
            }else {
                print("删除失败")
            }
        }
        
    }

    
}
